package database;

import java.sql.Statement;

import server.database.ConnectionDatabase;

/**
 * Done men mangler javadoc
 * @author daniel
 */

public class CreateTable {
	
	private String createString;
	private Statement st;
	
	public CreateTable() {
		try {
			st = ConnectionDatabase.getConnection().createStatement();			
		} catch (Exception e) {
			System.err.println("AppoitmentDB::Constructor:Exception: " + e.getMessage());
		}
	}
	
	public void createUserTable() {
		createString = "create table " + ConnectionDatabase.getNameDB() + ".person " +
		"(user_id INT NOT NULL GENERATED ALWAYS AS IDENTITY (START WITH 1)," +
		"name VARCHAR(40) NOT NULL, " +
		"dateOfBirth BIGINT, " +
		"email VARCHAR(40), " +
		"phone varchar(12)," +
		"department varchar(20)," +
		
		"PRIMARY KEY (user_id))";
		
		ConnectionDatabase.update(createString, st);
		System.out.println("User table handled");
	}


	public void createMessageTable() {
		createString = "create table " + ConnectionDatabase.getNameDB() + ".message " +
		"(message_id INT NOT NULL GENERATED ALWAYS AS IDENTITY (START WITH 1), " +
		"sendTime BIGINT, " +
		"subject VARCHAR(40), " +
		"description VARCHAR(160), " +
		"readTime BIGINT," +
		//0 = a message, 1=an invite
		"is_invite INT DEFAULT 0," +
		"answer INT," +
		"meeting_id INT," +
		"receiver_id INT," +
		
		"PRIMARY KEY (message_id)," +
		"FOREIGN KEY (receiver_id) REFERENCES " + ConnectionDatabase.getNameDB() + ".person(user_id) " +
				"ON DELETE SET NULL" +
				//"ON UPDATE CASCADE," +
		/*
		 *Due to last minute changes we removes the foreign key. This solution is to be able
		 *to send a message that a meeting has been deleted / removed 
		 */
		//"FOREIGN KEY (meeting_id) REFERENCES " + ConnectionDatabase.getNameDB() + ".meeting(meeting_id)" +
				//"ON DELETE SET NULL " +
				//"ON UPDATE CASCADE" + 
				")";

		ConnectionDatabase.update(createString, st);
		System.out.println("Message table handled");

	}

	public void createAppointmentsTable()  {
		createString = "create table " + ConnectionDatabase.getNameDB() + ".appointment " +
		"(appointment_id INT NOT NULL GENERATED ALWAYS AS IDENTITY (START WITH 1), " +
		"title VARCHAR(40), " +
		"description VARCHAR(160)," +
		"location VARCHAR(40),"+
		"reservation_id INT," +
		"owner_id INT," +
		
		"PRIMARY KEY (appointment_id)," +
		"FOREIGN KEY (reservation_id) REFERENCES " + ConnectionDatabase.getNameDB() + ".reservation(reservation_id), " +
				//"ON DELETE SET NULL, " +
				//"ON UPDATE CASCADE, " +
		"FOREIGN KEY (owner_id) REFERENCES " + ConnectionDatabase.getNameDB() + ".person(user_id) " +
				"ON DELETE SET NULL " +
				//"ON UPDATE CASCADE" +
				")";
		
		ConnectionDatabase.update(createString, st);
		System.out.println("Appointments table handled");

	}
	
	public void createMeetingTable()  {
		createString = "create table " + ConnectionDatabase.getNameDB() + ".meeting " +
		"(meeting_id INT NOT NULL GENERATED ALWAYS AS IDENTITY (START WITH 1000000000), " +
		"title VARCHAR(40), " +	
		"description VARCHAR(160)," +
		"location VARCHAR(40),"+
		"reservation_id INT," +
		"owner_id INT," +
		
		"PRIMARY KEY (meeting_id)," +
		"FOREIGN KEY (reservation_id) REFERENCES " + ConnectionDatabase.getNameDB() + ".reservation(reservation_id), " +
				//"ON DELETE SET NULL, " +
				//"ON UPDATE CASCADE, " +
		"FOREIGN KEY (owner_id) REFERENCES " + ConnectionDatabase.getNameDB() + ".person(user_id) " +
				//"ON UPDATE CASCADE" +
				")";
		
		ConnectionDatabase.update(createString, st);
		System.out.println("Meeting table handled");

	}
	
	public void createReservationTable()  {
		createString = "create table " + ConnectionDatabase.getNameDB() + ".reservation " +
		"(reservation_id INT NOT NULL GENERATED ALWAYS AS IDENTITY (START WITH 1), " +
		"starttime BIGINT, " +
		"endtime BIGINT, " +
		"room_id INT, " +
		
		"PRIMARY KEY (reservation_id), " +
		"FOREIGN KEY (room_id) REFERENCES " + ConnectionDatabase.getNameDB() + ".room(room_id) " +
				//"ON DELETE SET NULL " + 
				")";

		ConnectionDatabase.update(createString, st);
		System.out.println("Reservation table handled");
	}
	
	public void createSettingsTable() {
		createString = "create table " + ConnectionDatabase.getNameDB() + ".setting " +
		"(user_id INT," +
		"username VARCHAR(20) NOT NULL," +
		"password VARCHAR(20) NOT NULL," +
		"color_theme INT," +
		"sound_enabled INT," +
		
		"PRIMARY KEY(username)," +
		"UNIQUE(user_id)," +
		"FOREIGN KEY (user_id) REFERENCES " + ConnectionDatabase.getNameDB() + ".person(user_id) " +
				"ON DELETE SET NULL" + 
				")";

		ConnectionDatabase.update(createString, st);
		System.out.println("Setting table handled");

	}

	public void createRoomTable() {
		createString = "create table " + ConnectionDatabase.getNameDB() + ".room " +
		"(room_id INT NOT NULL GENERATED ALWAYS AS IDENTITY (START WITH 1)," +
		"name VARCHAR(40)," +
		"casual_name varchar(20)," +
		"location varchar(20)," +
		"capacity INT," +
		"description VARCHAR(20)," +
		
		"PRIMARY KEY(room_id))";

		ConnectionDatabase.update(createString, st);
		System.out.println("Room table handled");

	}
	
	public void createMeetingParticipant() {
		createString = "create table " + ConnectionDatabase.getNameDB() + ".meetingparticipant " +
		"(meeting_id INT NOT NULL, " +
		"participant_id INT NOT NULL, " +
		
		"PRIMARY KEY(meeting_id, participant_id))";

		ConnectionDatabase.update(createString, st);
		System.out.println("MeetingParticipant table handled");

	} 
	
	public static void main(String[] args) {
		CreateTable table = new CreateTable();
		
		table.createUserTable();
		table.createSettingsTable();
		
		table.createRoomTable();
		table.createReservationTable();
		
		table.createAppointmentsTable();
		table.createMeetingTable();
		
		table.createMessageTable();	
		table.createMeetingParticipant();
		
		DataHandler.runAfterConnect();
	}
}

